package com.noob.susu.excel.util;

import com.google.common.collect.Lists;
import com.noob.susu.excel.annotations.ExcelField;
import com.noob.susu.excel.annotations.ExcelHead;
import com.noob.susu.excel.entity.ExcelModelResult;
import com.noob.susu.excel.entity.FieldValueResult;
import com.noob.susu.excel.enums.EnumTransform;
import com.noob.susu.excel.enums.ErrorCodeEnum;
import com.noob.susu.excel.exception.ExcelResolveException;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.commons.lang3.time.DateUtils;
import org.apache.poi.POIXMLDocument;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.InputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @author shupeng_liao
 * @date 2019/10/23 17:58
 **/
@Slf4j
public class ExcelUtils {

    /**
     * 流转成workbook
     *
     * @param inputStream 流
     * @return org.apache.poi.ss.usermodel.Workbook
     */
    public static Workbook parseExcel(InputStream inputStream) {
        Workbook book;
        try {
            if (POIFSFileSystem.hasPOIFSHeader(inputStream)) {
                book = new HSSFWorkbook(inputStream);
            } else if (POIXMLDocument.hasOOXMLHeader(inputStream)) {
                book = new XSSFWorkbook(inputStream);
            } else {
                log.error("excel类型不支持");
                throw new ExcelResolveException(ErrorCodeEnum.NOT_SUPPORT);
            }
        } catch (Exception e) {
            log.error("解析失败，原因：", e);
            throw new ExcelResolveException(ErrorCodeEnum.NOT_SUPPORT);
        }
        return book;
    }


    /**
     * 根据对象获取解析成对象的list
     *
     * @param sheet sheet
     * @param clazz 需要解析成的对象
     * @return java.util.List<T> 对象的list
     */
    public static <T> List<T> getListFromSheet(Sheet sheet, Class<T> clazz, Boolean skipError) {

        if (null == sheet) {
            log.warn("sheet为空");
            return null;
        }

        // 得到数据的行数
        int rows = sheet.getLastRowNum();
        if (rows <= 0) {
            log.warn("sheet为空");
            return null;
        }
        //获取头注解
        ExcelHead annotation = clazz.getAnnotation(ExcelHead.class);
        //head开始的行数,即表头开始行数
        int headRowNum = null == annotation ? 0 : annotation.headRow();
        //开始的行
        int beginRow = null == annotation ? 0 : annotation.beginRow();
        //结束的行
        int endRow = null == annotation ? 0 : annotation.endRow();
        //开始的列
        int beginColumn = null == annotation ? 0 : annotation.beginColumn();
        //结束的列
        int endColumn = null == annotation ? 0 : annotation.endColumn();

        //实际需要读取的行数
        if (rows > endRow) {
            rows = endRow;
        }

        //获取表头对应的行
        Row row = sheet.getRow(headRowNum);
        if (null == row) {
            log.warn("head行数设置不正确");
            return null;
        }
        //获取表头字段所在的位置map
        Map<String, Integer> headLine = getHeadLine(row, beginColumn, endColumn);

        //获取下标和实体字段map
        Map<Integer, Field> classFiledMap = getClassFiledMap(clazz, headLine);

        List<T> resultList = new ArrayList<>();
        for (int i = beginRow + 1; i <= rows; i++) {
            //获取行
            Row currentRow = sheet.getRow(i);
            if (null == currentRow) {
                continue;
            }
            ExcelModelResult<T> object = null;
            try {
                object = getObject(clazz, classFiledMap, currentRow);
            } catch (Exception e) {
                if (skipError) {
                    continue;
                } else {
                    //抛出异常

                }
            }
            if (null != object) {
                resultList.add(object.getModel());
            }
        }
        return resultList;
    }


    /**
     * 转换对象
     *
     * @param clazz         转换对象
     * @param classFiledMap map
     * @param currentRow    row
     * @return T 转换对象
     */
    private static <T> ExcelModelResult<T> getObject(Class<T> clazz, Map<Integer, Field> classFiledMap, Row currentRow) {

        ExcelModelResult<T> excelModelResult = ExcelModelResult.getInstance();

        T entity;
        try {
            entity = clazz.newInstance();
        } catch (Exception e) {
            excelModelResult.setSuccess(false);
            excelModelResult.setErrorMessages(Lists.newArrayList(ErrorCodeEnum.OBJECT_INIT_EXCEPTION.getMsg()));
            return excelModelResult;
        }

        //错误信息
        ArrayList<String> errorMessages = Lists.newArrayList();
        excelModelResult.setModel(entity);
        excelModelResult.setErrorMessages(errorMessages);

        for (Integer index : classFiledMap.keySet()) {
            Field field = classFiledMap.get(index);
            Cell cell = currentRow.getCell(index);
            if (field != null && field.isAnnotationPresent(ExcelField.class)) {
                ExcelField excelField = field.getDeclaredAnnotation(ExcelField.class);
                String errorMsg = "行数:{0},列数:{1},对应表头字段:{2},";
                errorMsg = transString(errorMsg, currentRow.getRowNum(), index, excelField.name());

                //根据字段类型获取对应的值
                FieldValueResult<Object> value = getValue(field.getType(), cell, excelField);
                if (!value.getSuccess()) {
                    excelModelResult.setSuccess(false);
                    errorMessages.add(errorMsg + value.getErrorMessage());
                    continue;
                }

                try {
                    field.set(entity, value.getModel());
                } catch (Exception e) {
                    errorMessages.add(errorMsg + transString(ErrorCodeEnum.OBJECT_SET_EXCEPTION.getMsg(), clazz.getName(), field.getName()));
                    excelModelResult.setSuccess(false);
                }
            }
        }
        return excelModelResult;
    }


    /**
     * 获取对象的字段map
     *
     * @param clazz 对象
     * @return java.util.Map
     */
    private static Map<Integer, Field> getClassFiledMap(Class<?> clazz, Map<String, Integer> headLineMap) {
        Field[] allFields = clazz.getDeclaredFields();
        // 定义一个map用于存放列的序号和field
        Map<Integer, Field> fieldsMap = new HashMap<>(allFields.length);
        for (int i = 0; i < allFields.length; i++) {
            Field field = allFields[i];
            // 将有注解的field存放到map中
            if (field.isAnnotationPresent(ExcelField.class)) {
                ExcelField annotation = field.getDeclaredAnnotation(ExcelField.class);
                if (null == annotation || annotation.name().length == 0) {
                    continue;
                }
                String[] names = annotation.name();
                boolean hasField = false;
                for (int j = 0; j < names.length; j++) {
                    Integer realIndex = headLineMap.get(names[j]);
                    if (realIndex != null) {
                        // 设置类的私有字段属性可访问
                        field.setAccessible(true);
                        fieldsMap.put(realIndex, field);
                        hasField = true;
                        break;
                    }
                }
                if (!hasField) {
                    log.warn("字段:{},name:{},在文件中未发现对应的列", field.getName(), names);
                }
            }
        }
        return fieldsMap;
    }

    /**
     * 获取头的行的数据
     *
     * @param cells 行
     * @return map
     */
    private static Map<String, Integer> getHeadLine(Row cells, int beginColumn, int endColumn) {
        Map<String, Integer> map = new HashMap<>(endColumn - beginColumn);
        int lastCellNum = cells.getLastCellNum();
        if (endColumn < lastCellNum) {
            lastCellNum = endColumn;
        }
        for (int i = beginColumn; i < lastCellNum; i++) {
            map.put(cells.getCell(i).getStringCellValue(), i);
        }
        return map;
    }


    /**
     * 根据正则获取对应值
     *
     * @param cellStringValue cell值
     * @param pattern         正则
     * @return java.lang.String 字符串
     */
    private static String getValueByRegex(String cellStringValue, String pattern) {
        //根据正则获取值
        if (StringUtils.isNotBlank(pattern)) {
            Pattern r = Pattern.compile(pattern);
            Matcher m = r.matcher(cellStringValue);
            if (m.find()) {
                return m.group(0);
            }
        }
        return cellStringValue;
    }

    /**
     * 获取对应的值
     *
     * @param fieldType  字段类型
     * @param cell       单元格
     * @param excelField 注解
     * @return java.lang.Object 对应的值
     */
    private static FieldValueResult<Object> getValue(Class<?> fieldType, Cell cell, ExcelField excelField) {

        FieldValueResult<Object> result = FieldValueResult.getInstance();
        String cellValue = getCellValueByCell(cell);

        //默认值
        if (StringUtils.isEmpty(cellValue) && StringUtils.isEmpty(excelField.defaultValue())) {
            cellValue = excelField.defaultValue();
        }

        //校验是否符合条件(是必填并且无默认值的数据)
        if (StringUtils.isEmpty(cellValue) && excelField.required()) {
            result.setSuccess(false);
            result.setErrorMessage(ErrorCodeEnum.FIELD_VALUE_NULL_ERROR.getMsg());
            return result;
        }

        //为空且无默认值,不必填,直接返回
        if (StringUtils.isEmpty(cellValue)) {
            return result;
        }

        //根据正则获取值
        if (!StringUtils.isEmpty(cellValue) && StringUtils.isNotBlank(excelField.regex())) {
            cellValue = getValueByRegex(cellValue, excelField.regex());
        }

        //根据枚举匹配值
        Class<? extends EnumTransform> enumType = excelField.enumType();

        //不是默认的class
        if (!StringUtils.isEmpty(cellValue) && !enumType.equals(EnumTransform.class)) {
            try {
                EnumTransform[] enumConstants = enumType.getEnumConstants();
                if (enumConstants.length > 0) {
                    cellValue = enumConstants[0].getCodeByDesc(cellValue);
                }
            } catch (Exception e) {
                log.error("枚举转换异常", e);
                result.setSuccess(false);
                result.setErrorMessage(ErrorCodeEnum.ENUM_CONVERTER_ERROR.getMsg());
                return result;
            }
        }
        result.setModel(getValueByFieldType(fieldType, cellValue, cell, excelField));
        return result;
    }


    /**
     * 获取日期值
     *
     * @param cell       cell
     * @param excelField excelField
     * @return java.util.Date
     */
    private static Date getValueByDateType(Cell cell, ExcelField excelField) {
        //时间的类型转换
        String[] dateParsePatterns = excelField.dateParsePatterns();
        return formatDate(cell, dateParsePatterns);
    }


    /**
     * 获取字符串类型默认值
     *
     * @param cellValue  值
     * @param excelField excelField
     * @param cell       单元格
     * @return java.lang.String
     */
    private static String getValueByStringType(String cellValue, ExcelField excelField, Cell cell) {
        //时间转换前类型
        String[] dateParsePatterns = excelField.dateParsePatterns();
        //转换后类型
        String dateFormatPattern = excelField.dateFormatPattern();

        //字符串类型转换前转换后都有
        if (dateParsePatterns.length > 0 && StringUtils.isNotBlank(dateFormatPattern)) {
            //获取解析前日期
            Date date = formatDate(cell, dateParsePatterns);
            if (null != date) {
                //解析后日期
                cellValue = DateFormatUtils.format(date, dateFormatPattern);
            }
        }
        return cellValue;
    }

    /**
     * 转换为基础类型的值
     *
     * @param fieldType 字段类型
     * @param cellValue 单元格字符串值
     * @return java.lang.Object 字段类型对应的值
     */
    private static Object getValueByFieldType(Class<?> fieldType, String cellValue, Cell cell, ExcelField excelField) {

        //字符串
        if (String.class == fieldType) {
            return getValueByStringType(cellValue, excelField, cell);
        }

        //时间类型
        if (Date.class == fieldType) {
            return getValueByDateType(cell, excelField);
        }

        if (BigDecimal.class == fieldType) {
            return new BigDecimal(cellValue);
        } else if ((Integer.TYPE == fieldType) || (Integer.class == fieldType)) {
            return Integer.valueOf(cellValue);
        } else if ((Long.TYPE == fieldType) || (Long.class == fieldType)) {
            return new BigDecimal(cellValue).longValue();
        } else if ((Float.TYPE == fieldType) || (Float.class == fieldType)) {
            return Float.valueOf(cellValue);
        } else if ((Short.TYPE == fieldType) || (Short.class == fieldType)) {
            return Short.valueOf(cellValue);
        } else if ((Double.TYPE == fieldType) || (Double.class == fieldType)) {
            return Double.valueOf(cellValue);
        } else {
            log.error("不支持的转换类型,cell:{}", cellValue);
            return null;
        }
    }

    /**
     * 获取单元格各类型值，返回字符串类型
     *
     * @param cell 单元格
     * @return java.lang.String 字符串类型数据
     */
    private static String getCellValueByCell(Cell cell) {
        //判断是否为null
        if (cell == null) {
            return null;
        }

        String cellValue;
        CellType cellType = cell.getCellTypeEnum();
        switch (cellType) {
            //字符串类型
            case STRING:
                cellValue = cell.getStringCellValue().trim();
                cellValue = StringUtils.isEmpty(cellValue) ? "" : cellValue;
                break;
            //布尔类型
            case BOOLEAN:
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            //数值类型
            case NUMERIC:
                //判断日期类型
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    cellValue = DateFormatUtils.format(cell.getDateCellValue(), "yyyy-MM-dd");
                } else {  //否
                    cellValue = new DecimalFormat("#.######").format(cell.getNumericCellValue());
                }
                break;
            case BLANK:
                cellValue = "";
                break;
            default:
                cellValue = cell.toString();
                break;
        }
        return cellValue;
    }

    /**
     * 时间转换
     */
    private static Date formatDate(Cell cell, String[] dateParsePatterns) {
        CellType cellTypeEnum = cell.getCellTypeEnum();
        switch (cellTypeEnum) {
            case STRING:
                if (dateParsePatterns.length > 0) {
                    return parseDate(cell.getStringCellValue(), dateParsePatterns);
                }
                return parseDate(cell.getStringCellValue(), "yyyy-MM-dd HH:mm:ss", "yyyy/MM/dd HH:mm:ss", "yyyy-MM-dd", "yyyy/MM/dd", "yyyyMMdd");
            case NUMERIC:
                return cell.getDateCellValue();
            default:
                log.error("暂不支持的时间类型,请添加,cell:{}", cell);
                return null;
        }
    }


    /**
     * 解析时间
     *
     * @param stringDate    字符串时间类型
     * @param parsePatterns 时间表达式
     * @return java.util.Date 时间
     */
    private static Date parseDate(String stringDate, String... parsePatterns) {
        try {
            return DateUtils.parseDate(stringDate, parsePatterns);
        } catch (ParseException e) {
            log.error("时间解析异常:", e);
        }
        return null;
    }

    /**
     * 打印方法
     *
     * @param data           数据
     * @param replaceStrings 替换数据
     * @return java.lang.String 替换后的数据
     */
    private static String transString(String data, Object... replaceStrings) {
        if (StringUtils.isBlank(data)) {
            return data;
        }
        for (int i = 0; i < replaceStrings.length; i++) {
            String str = "{" + i + "}";
            data = data.replace(str, replaceStrings[i].toString());
        }
        return data;
    }
}
